Database 2
Why?
SQL is the language we use to talk and interact with relational databases. Database skills are essential for data scientists who need to extract, transform, and analyze data efficiently. This course builds on the database design fundamentals learned in Database 1, focusing on the practical implementation using SQL and database management systems that you'll probably use daily in real-world data science applications.
What?
This course provides a comprehensive introduction to SQL and database management systems. You will learn to write SQL queries, implement database constraints, create stored procedures, and manage database security. The focus is on practical skills that bridge database theory with actual implementation.
Curriculum:
Introduction to SQL
Fundamentals of SQL, basic query syntax, SELECT statements, filtering data with WHERE, and understanding SQL data types and operators.
Introduction to DBMS
Overview of database management systems, their architecture, functionality, and how to interact with databases through management interfaces.
SQL Aggregates
Using aggregate functions (COUNT, SUM, AVG, MIN, MAX), GROUP BY clause, HAVING filters, and creating summary reports from data.
SQL Join Operations
Techniques for combining data from multiple tables using INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and CROSS JOIN operations.
SQL Subqueries
Writing nested queries, correlated subqueries, and using subqueries in SELECT, FROM, WHERE, and HAVING clauses for complex data retrieval.
Constraints and Triggers
Implementing data integrity with constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK), and using triggers for automated database responses to events.
Views and Access Control
Creating and using views to simplify queries and enhance security, and implementing user access controls and permissions management.
Functions and Stored Procedures
Designing and implementing user-defined functions and stored procedures for code reusability and complex operations execution.
Notes
SQL syntax is not hard, but this language requires a different way of thinking than regular programming you know. It is a declarative language, meaning you state what you want, unlike imperative languages like C++ where you write specifically "how to do it". (however SQL also contains procedural extension, which is imperative) Where to apply what you learn? In university, there is no specific software used, but it is highly recommended you choose one and practice in it, because there are many minor syntax differences between different systems.
